﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class ApplicationDetailHandler
    {
        
        public static DataTable BindDataTableForApplication(int appid)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };
                string sql = "SELECT crm_applicationcontact.app_id, crm_contact.FirstName, crm_contact.MiddleName, crm_contact.SurName, crm_contact.Mobile, crm_contact.HomePhone, crm_contact.Email, (SELECT  app_LoanValue FROM  crm_loans WHERE (app_id = crm_applicationcontact.app_id)LIMIT 1) AS loanvalue,(SELECT app_LoanAmount FROM  crm_loaninfo WHERE (app_id = crm_applicationcontact.app_id)) AS LoanAmount, crm_applicationextend.LoadNo, crm_applicationextend.Folder,  crm_applicationextend.AwaitDocs, crm_lastresult.ResultDescription, (select count(*) from crm_vehicles where crm_vehicles.app_id=crm_applicationextend.app_id) as Motor,(select count(*) from crm_realestate where crm_realestate.app_id=crm_applicationextend.app_id) as Property FROM  crm_lastresult LEFT OUTER JOIN crm_applicationextend ON crm_lastresult.ResultID = crm_applicationextend.ResultID RIGHT OUTER JOIN crm_applicationcontact LEFT OUTER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID ON crm_applicationextend.app_id = crm_applicationcontact.app_id WHERE (crm_applicationcontact.NoInApplication = 1) AND (crm_applicationcontact.app_id = @app_id)";
                dt = DBhelper.ExecuteDataset(sql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static bool UpdateAppDetailData(string appid, string loanAmount, string loanValue, string firstName, string middleName, string surName, string mobile, string homePhone, string email, string resultDescription, string loadNo, string awaitDocs, string folder)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                DBhelper.SetParameterForApp("@app_LoanAmount",loanAmount),
                DBhelper.SetParameterForApp("@app_LoanValue",loanValue),
                DBhelper.SetParameterForApp("@FirstName",firstName),
                DBhelper.SetParameterForApp("@MiddleName",middleName),
                DBhelper.SetParameterForApp("@SurName",surName),
                DBhelper.SetParameterForApp("@Mobile",mobile),
                DBhelper.SetParameterForApp("@HomePhone",homePhone),
                DBhelper.SetParameterForApp("@Email",email),
                DBhelper.SetParameterForApp("@ResultDescription",resultDescription),
                DBhelper.SetParameterForApp("@LoadNo",loadNo),
                DBhelper.SetParameterForApp("@AwaitDocs",awaitDocs),
                DBhelper.SetParameterForApp("@Folder",folder),
                DBhelper.SetParameterForApp("@app_id", appid),
                };

                string sqlQuery = "update crm_applicationextend set crm_applicationextend.LoadNo=@LoadNo,crm_applicationextend.AwaitDocs=@AwaitDocs,crm_applicationextend.Folder=@Folder where crm_applicationextend.app_id=@app_id; " +
                                  "update crm_loans,crm_loaninfo,crm_contact,crm_lastresult SET crm_loans.app_LoanValue=@app_LoanValue,crm_loaninfo.app_LoanAmount=@app_LoanAmount,crm_contact.FirstName=@FirstName,crm_contact.MiddleName=@MiddleName,crm_contact.SurName=@SurName,crm_contact.Mobile=@Mobile,crm_contact.HomePhone=@HomePhone,crm_contact.Email=@Email,crm_lastresult.ResultDescription=@ResultDescription WHERE crm_loans.app_id=@app_id and crm_loaninfo.app_id=@app_id and crm_contact.ContactID=(select ContactID from crm_applicationcontact where crm_applicationcontact.app_id=@app_id) and ResultID=(select ResultID from crm_applicationextend where app_id=@app_id)";
                DBhelper.ExecuteNonQuery(sqlQuery, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        public static DataSet SelectApplicationDetail(int app_id)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };

                StringBuilder strQuery = new StringBuilder("Select crm_application.app_id, crm_application.CreatedDate,DesiredAmount , (select sum(tblSecurity.total) from (select count(*) as total from am_securityproperty ")
                            .Append("INNER JOIN am_loan1 ON am_securityproperty.LoanID=am_loan1.LoanID inner join crm_application on am_loan1.app_id=crm_application.app_id and crm_application.app_id=@app_id ")
                            .Append("UNION ALL select count(*) as total from am_securityvehicle inner JOIN am_loan1 ON  am_securityvehicle.LoanID=am_loan1.LoanID ")
                            .Append("INNER join crm_application on am_loan1.app_id=crm_application.app_id and crm_application.app_id=@app_id ")
                            .Append("UNION ALL select count(*) as total from am_securitycompany inner JOIN am_loan1 ON am_securitycompany.LoanID=am_loan1.LoanID ")
                            .Append("INNER join crm_application on am_loan1.app_id=crm_application.app_id and crm_application.app_id=@app_id ) tblSecurity ) as Security ")
                            .Append("FROM crm_application where crm_application.app_id=@app_id");
                ds = DBhelper.ExecuteDataset(strQuery.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        //Delete application for given app_id
        public static bool DeleteApplication(int app_id)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };
                StringBuilder query = new StringBuilder("Delete from crm_application where app_id =(select applicationID from crm_applicationappform where appformid=@app_id); ")
                        .Append("Delete From crm_appcontactchecklist where app_id =(select applicationID from crm_applicationappform where appformid=@app_id); ")
                        .Append("Delete from crm_applicationcontact where  app_id =(select applicationID from crm_applicationappform where appformid=@app_id); ")
                        .Append("Delete from crm_applicationappform  where ApplicationId=@app_id; ")
                        .Append("Delete from crm_contact where app_id=@app_id;");
                DBhelper.ExecuteNonQuery(query.ToString(), para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        public static DataTable BindApplicationForSMSFields(Int64 appid)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };
                string sqlqry = "select a.*, c.* from crm_application a inner join crm_applicationcontact ac on ac.app_id=a.app_id inner join crm_contact c on c.ContactID=ac.ContactID where a.app_id=@app_id";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static bool DeleteMultipleApplication(string applications, int currentContactId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@currentContactId", currentContactId),
                    DBhelper.SetParameter("@applications", applications),
                };
                StringBuilder query = new StringBuilder("Update crm_application SET deletedFlag=1 where app_id IN (@applications); ")
                        .Append("Update crm_applicationcontact SET deletedFlag=1 where ContactID=@currentContactId and app_id IN (@applications); ")
                        .Append("Update crm_contact SET deletedFlag=1 where app_id IN (@applications);");
                DBhelper.ExecuteNonQuery(query.ToString(), para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }
    }
}

